const { query } = require("./db.js");

class StaffDao {
    // 获取所有员工
    async getAllStaff(department = null, entryTime = null) {
        try {
            let sql = `
                SELECT 
                    user.*, 
                    position.pos_name as position,
                    position.pos_salary as salary -- 从position表获取基本工资
                FROM user 
                JOIN position ON user.user_position_id = position.pos_id
                WHERE user_role_id IN (3, 4)
            `;
            const params = [];
            const conditions = [];

            // 添加部门筛选
            if (department && department !== 'all') {
                conditions.push('position.pos_name LIKE ?');
                params.push('%' + department + '%');
            }

            // 添加入职时间筛选
            if (entryTime) {
                 // 判断是年还是年月进行查询
                if (entryTime.length === 4) { // 年份
                    conditions.push('YEAR(user_entrytime) = ?');
                    params.push(entryTime);
                } else if (entryTime.length === 7) { // 年月
                    conditions.push('DATE_FORMAT(user_entrytime, "%Y-%m") = ?');
                    params.push(entryTime);
                }
                // 如果不是年或年月格式，忽略此条件
            }

            // 构建完整的WHERE子句
            if (conditions.length > 0) {
                sql += ' AND ' + conditions.join(' AND ');
            }

            console.log("Executing SQL:", sql);
            console.log("Parameters:", params);

            const rows = await query(sql, params);
            const formattedRows = rows.map(row => {
                return {
                    ...row,
                    // year_month: row.rec_date ? new Date(row.rec_date).toISOString().split('T')[0] : null // 这行似乎是多余的，移除
                };
            });
            return formattedRows;
        } catch (error) {
            console.error('Error getting all staff:', error);
            throw error;
        }
    }

    // 根据ID获取员工
    async getStaffById(id) {
        try {
            const sql = 'SELECT user.*, position.pos_name as position FROM user JOIN position ON user.user_position_id = position.pos_id WHERE user_id = ?';
            const rows = await query(sql, [id]);
            return rows[0];
        } catch (error) {
            console.error('Error getting staff by id:', error);
            throw error;
        }
    }

    // 检查账号是否存在
    async checkAccountExists(user_account) {
        try {
            const sql = 'SELECT COUNT(*) as count FROM user WHERE user_account = ?';
            const rows = await query(sql, [user_account]);
            return rows[0].count > 0;
        } catch (error) {
            console.error('Error checking account exists:', error);
            throw error;
        }
    }

    // 添加新员工
    async addStaff(staff) {
        try {
            const sql = 'INSERT INTO user (user_account, user_name, user_sex_id, user_position_id, user_entrytime, user_role_id) VALUES (?, ?, ?, ?, ?, 4)';
            const params = [staff.user_account, staff.user_name, staff.user_sex_id, staff.user_position_id, staff.user_entrytime];
            const result = await query(sql, params);
            return result;
        } catch (error) {
            console.error('Error adding staff:', error);
            throw error;
        }
    }

    // 更新员工信息
    async updateStaff(id, staff) {
        try {
            const sql = 'UPDATE user SET user_account = ?, user_name = ?, user_sex_id = ?, user_position_id = ?, user_entrytime = ? WHERE user_id = ?';
            const params = [staff.user_account, staff.user_name, staff.user_sex_id, staff.user_position_id, staff.user_entrytime, id];
            const result = await query(sql, params);
            return result;
        } catch (error) {
            console.error('Error updating staff:', error);
            throw error;
        }
    }

    // 删除员工
    async deleteStaff(id) {
        try {
            const sql = 'DELETE FROM user WHERE user_id = ?';
            const result = await query(sql, [id]);
            return result;
        } catch (error) {
            console.error('Error deleting staff:', error);
            throw error;
        }
    }

    // 获取所有职位
    async getAllPositions() {
        try {
            const sql = 'SELECT * FROM position';
            return await query(sql);
        } catch (error) {
            console.error('Error getting all positions:', error);
            throw error;
        }
    }
}

module.exports = new StaffDao();